﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Crosland.Automotriz.Core;

namespace Crosland.Automotriz.Data.Dao.Linq
{
    public class clsT_Imp_Importacion_Back_OrderDAO
    {
        AutomotrizEntities ContextoDeObjs = new AutomotrizEntities();
        public List<T_IMP_IMPORTACION_BACK_ORDER_DET> ListBackOrder(int ID_ORG_VTA, String NUM_PEDIDO_ORG)
        {
            ContextoDeObjs.CommandTimeout = 6000;
            List<T_IMP_IMPORTACION_BACK_ORDER_DET> lista = new List<T_IMP_IMPORTACION_BACK_ORDER_DET>();
            var queryText = String.Format("SELECT ID_IMP_DET,COD_MATERIAL,DES_MATERIAL_IMP,FOB_ME,CANT_COT,CANT_EMB,DIFERENCIA,CANTIDAD=CASE WHEN DIFERENCIA<0 THEN 0 ELSE DIFERENCIA END,TOTAL_FOB_ME=CASE WHEN TOTAL_FOB_ME<0 THEN 0 ELSE TOTAL_FOB_ME END  FROM DBO.FU_BACK_ORDER({0},'{1}')", ID_ORG_VTA, NUM_PEDIDO_ORG);
                var q = ContextoDeObjs.ExecuteStoreQuery<T_IMP_IMPORTACION_BACK_ORDER_DET>(queryText);
                lista = q.ToList();            
            return lista;
        }


        public decimal TotalBackOrder(int ID_ORG_VTA, String NUM_PEDIDO_ORG)
        {

            try
            {
                var query = String.Format("SELECT sum(TOTAL_FOB_ME)FROM DBO.FU_BACK_ORDER({0},'{1}')", ID_ORG_VTA, NUM_PEDIDO_ORG);
                var q = ContextoDeObjs.ExecuteStoreQuery<decimal>(query);
                return q.FirstOrDefault();
            }
            catch (Exception)
            {

                return 0;
            }
         
        }


        public List<T_IMP_IMPORTACION_BACK_ORDER_CAB> ListaCab(int ID_ORG_VTA, int ID_IMP_IMPORTACION_CAB)
        {

            List<T_IMP_IMPORTACION_BACK_ORDER_CAB> lista = new List<T_IMP_IMPORTACION_BACK_ORDER_CAB>();

            var queryText = String.Format("SELECT tiic.ID_ORG_VTA,tiic.NUM_PEDIDO_ORIG,tiic.FEC_PEDIDO,tiic.COD_IMP_ESTADO,tiic.FEC_EMBARQUE_EST,tiic.FEC_LLEG_EST_PUERTO,tiic.FEC_LLEG_PUERTO,tiic.FEC_LLEG_ALMACEN,tiic.FEC_EMBARQUE,tiic.ID_PROVEEDOR,tp.NOM_PROVEEDOR,ID_TIPO_TRANSPORTE=tmtgd.ID_GENERAL_DET,TIPO_TRANSPORTE=tmtgd.DES_CORTA   FROM T_IMP_IMPORTACION_CAB tiic INNER JOIN TD_PROVEEDOR tp ON tiic.ID_PROVEEDOR=tp.ID_PROVEEDOR LEFT JOIN T_MAE_TABLA_GENERAL_DET tmtgd ON tmtgd.ID_GENERAL_DET=tiic.ID_TIPO_TRANSPORTE  WHERE tiic.ID_ORG_VTA={0} AND TIIC.ID_IMP_IMPORTACION_CAB={1}", ID_ORG_VTA, ID_IMP_IMPORTACION_CAB);
                var q = ContextoDeObjs.ExecuteStoreQuery<T_IMP_IMPORTACION_BACK_ORDER_CAB>(queryText);
                lista = q.ToList();           
            return lista;

        }


        public int VerificaExistenciaNumeroPedido(String num_pedido) {

            var sql = String.Format("SELECT COUNT(*) FROM T_IMP_IMPORTACION_CAB tiic WHERE tiic.NUM_PEDIDO='{0}' AND tiic.COD_IMP_ESTADO!='07'", num_pedido);
            var q = ContextoDeObjs.ExecuteStoreQuery<int>(sql);
            int count=q.FirstOrDefault();
            return count;
        }



        public int VerificaExistenciaBackOrder(String num_pedido) {
            var sql = String.Format("SELECT COUNT(*) FROM T_IMP_IMPORTACION_CAB tiic WHERE TIIC.NUM_PEDIDO_ORIG='{0}' AND tiic.COD_IMP_ESTADO='03'", num_pedido);
            var q = ContextoDeObjs.ExecuteStoreQuery<int>(sql);
            int count = q.FirstOrDefault();
            return count;       
        
        }

    }
}
